import hashlib

from flask import Flask, request, json
from sqlalchemy import create_engine, Table, Select, or_, func
from sqlalchemy.orm import sessionmaker, declarative_base, scoped_session

app = Flask(__name__)

# 创建引擎，链接数据库
engine = create_engine('mysql+pymysql://root:admin@127.0.0.1:3306/mumu', echo=True)
# 打开数据库的连接会话
session = sessionmaker(engine)
# 保证线程安全
db_session = scoped_session(session)
# 获取基类
Base = declarative_base()


class User(Base):
    # 表结构反射加载
    __table__ = Table("user", Base.metadata, autoload_with=engine)

sql = Select(User).filter_by(username = '宋孝斌')
print(sql)
result = db_session.query(User).filter(or_(User.id == 1, User.id == 2)).all()
for r in result:
    print(r.username)

# result2 = db_session.query(User).limit(4).all()
# result2 = db_session.query(User).limit(4).offset(2).all()
# result2 = db_session.query(User).filter(User.id < 5).all()

# order by
# result2 = db_session.query(User).order_by(User.id.desc()).all()

# like
result2 = db_session.query(User).filter(User.username.like('宋%')).order_by(User.id.asc()).all()
for e in result2:
    print(e.id)
    print(e.username)

# sum
s = db_session.query(func.sum(User.id)).first()
print(s)


if __name__ == '__main__':
    app.run(debug=True)